import re
import os

# 字段类型的映射关系
mapping = {
    "int": "bigint",
    "double": "double",
    "varchar": "string",
    "datetime": "TIMESTAMP(3)",
    "decimal": "double",
    "char": "string"
}


# 将mysql类型转换成hvie类型的工具
def mysql_type_to_hive_type(type_str: str):
    for mysql_type, hive_type in mapping.items():
        if mysql_type in type_str:
            return hive_type

    return "string"


if __name__ == '__main__':

    for mysql_ddl_file in os.listdir("../ods/mysql_ddl"):

        with open(f"../ods/mysql_ddl/{mysql_ddl_file}", mode="r", encoding="utf-8") as file:
            sql_str = file.read().replace("`", "")

            # 源库的表名
            old_table_name = re.search("TABLE (.*?)\(", sql_str, re.S).group(1).strip()

            # hive的表名
            hive_table_name = f"ods_{old_table_name}"

            # hive中表的路径
            hive_table_path = f"/daas/motl/ods/{hive_table_name}"

            # 查找列
            columns = re.search("\((.*)PRIMARY KEY", sql_str, re.S).group(1)
            # 去掉带都逗号的列的类型
            columns = columns.replace("decimal(10,2)", "decimal")

            # 按都好切分列
            columns_split = columns.split(",")[:-1]

            hive_columns_list = []

            mysql_columns_list = []

            hive_columns_name_and_type_list = []

            # 循环所有的列
            for line in columns_split:
                line = line.strip()
                name = line.split(" ")[0]
                type = line.split(" ")[1]
                hive_type = mysql_type_to_hive_type(type)

                hive_columns_list.append(f"    {name} {hive_type}")

                mysql_columns_list.append(f'"{name}"')

                hive_columns_name_and_type_list.append('{"name": "%s","type": "%s"}' % (name, hive_type))

            # 拼接出hive的列
            hive_columns = ",\n".join(hive_columns_list)

            # mysql列名字符串
            mysql_columns = ",\n".join(mysql_columns_list)

            # datax中hive的类型
            hive_columns_name_and_type = ",\n".join(hive_columns_name_and_type_list)

            #################读取hive建表的模板，构建hive建表语句#############################
            # 读取hive建表的模板，构建hive建表语句
            with open("template/hive_ods_ddl_template.sql", mode="r", encoding="utf-8") as temp:
                hive_ddl_template = temp.read()

                # 提交表名
                hive_ddl_template = hive_ddl_template.replace("{table_name}", hive_table_name)
                # 替换字段
                hive_ddl_template = hive_ddl_template.replace("{columns}", hive_columns)
                # 替换表的路径
                hive_ddl_template = hive_ddl_template.replace("{table_path}", hive_table_path)

            # 保存hive的建表语句
            with open(f"../ods/ddl/{hive_table_name}.sql", mode="w", encoding="utf-8") as ddl:
                ddl.write(hive_ddl_template)
                ddl.flush()

            #################读取datax的模板，构建datax json脚本#############################

            with open("template/mysql_hive_datax_template.json", mode="r", encoding="utf-8") as temp:
                datax_template = temp.read()
                datax_template = datax_template.replace("{mysql_columns}", mysql_columns)
                datax_template = datax_template.replace("{mysql_table_name}", old_table_name)
                datax_template = datax_template.replace("{hive_table_path}", hive_table_path)
                datax_template = datax_template.replace("{hive_column_name_and_type}", hive_columns_name_and_type)

            # 保存datax
            with open(f"../ods/datax/mysql_hive_{old_table_name}.json", mode="w", encoding="utf-8") as ddl:
                ddl.write(datax_template)
                ddl.flush()

            #################读取shell的模板，构建shell脚本#############################

            with open("template/start_mysql_hive_datax_sh_template.sh", mode="r", encoding="utf-8",
                      newline="\n") as temp:
                shell_template = temp.read()

                shell_template = shell_template.replace("{hive_table_path}", hive_table_path)
                shell_template = shell_template.replace("{datax_path}", f"mysql_hive_{old_table_name}.json")
                shell_template = shell_template.replace("{hive_table_name}", hive_table_name)

            # 保存shell
            with open(f"../ods/bin/start_mysql_hive_{old_table_name}_datax.sh", mode="w", encoding="utf-8",
                      newline="\n") as ddl:
                ddl.write(shell_template)
                ddl.flush()


            #################读取shell的模板，构建shell脚本#############################

            with open("template/flink_ods_cdc_ddl_template.sql", mode="r", encoding="utf-8",
                      newline="\n") as temp:
                shell_template = temp.read()

                shell_template = shell_template.replace("{ods_cdc_table_name}", f"ods_cdc_{old_table_name}")
                shell_template = shell_template.replace("{columns}", hive_columns)
                shell_template = shell_template.replace("{mysql_table_name}", old_table_name)


            # 保存shell
            with open(f"../ods/flink_ods_cdc_ddl/ods_cdc_{old_table_name}.sql", mode="w", encoding="utf-8",
                      newline="\n") as ddl:
                ddl.write(shell_template)
                ddl.flush()

            #################读取shell的模板，构建shell脚本#############################

            with open("template/flink_ods_ddl_tem[late.sql", mode="r", encoding="utf-8",
                      newline="\n") as temp:
                shell_template = temp.read()

                shell_template = shell_template.replace("{ods_table_name}", f"ods_{old_table_name}")
                shell_template = shell_template.replace("{columns}", hive_columns)


            # 保存shell
            with open(f"../ods/flink_ods_ddl/ods_{old_table_name}.sql", mode="w", encoding="utf-8",
                      newline="\n") as ddl:
                ddl.write(shell_template)
                ddl.flush()


                #################读取shell的模板，构建shell脚本#############################

            with open("template/flink_ods_dql_template.sql", mode="r", encoding="utf-8",
                      newline="\n") as temp:
                shell_template = temp.read()

                shell_template = shell_template.replace("{flink_ods_table_name}", f"ods_{old_table_name}")
                shell_template = shell_template.replace("{flink_ods_cdc_table_name}", f"ods_cdc_{old_table_name}")


            # 保存shell
            with open(f"../ods/flink_ods_dql/ods_{old_table_name}.sql", mode="w", encoding="utf-8",
                      newline="\n") as ddl:
                ddl.write(shell_template)
                ddl.flush()